﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace UserSQLGenerator
{
    class DBConnect
    {
        private MySqlConnection connection;
        private string server;
        private string database;
        private string uid;
        private string password;

        //Constructor
        public DBConnect()
        {
            Initialize();
        }

        //Initialize values
        private void Initialize()
        {
            server = "taz.harding.edu";
            database = "team5";

            Console.WriteLine("Username: ");
            uid = Console.ReadLine();

            Console.WriteLine("Password: ");
            password = Console.ReadLine();

            string connectionString;
            connectionString = "SERVER=" + server + ";" + "DATABASE=" +
            database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

            connection = new MySqlConnection(connectionString);
        }

        //open connection to database
        private bool OpenConnection()
        {
            try
            {
                connection.Open();
                return true;
            }
            catch (MySqlException ex)
            {
                //When handling errors, you can your application's response based 
                //on the error number.
                //The two most common error numbers when connecting are as follows:
                //0: Cannot connect to server.
                //1045: Invalid user name and/or password.
                switch (ex.Number)
                {
                    case 0:
                        Console.WriteLine("Cannot connect to server.  Contact administrator" + Environment.NewLine);
                        break;

                    case 1045:
                        Console.WriteLine("Invalid username/password, please try again" + Environment.NewLine);
                        break;
                }
                return false;
            }
        }

        //Close connection
        private bool CloseConnection()
        {
            try
            {
                connection.Close();
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.Message + Environment.NewLine);
                return false;
            }
        }

        //Insert statement
        public void ExampleInsert()
        {
            string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";

            //open connection
            if (this.OpenConnection() == true)
            {
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //Execute command
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }
        public void InsertStudents(List<string>[] list)
        {
            string query;

            //open connection
            if (this.OpenConnection() == true)
            {
                for (int i = 0; i < list[0].Count(); i++)
                {
                    query = "INSERT INTO Student VALUES("
                        + "'" + list[0][i] + "', "
                        + "'" + list[1][i] + "', "
                        + "'" + list[2][i] + "', "
                        + "'" + list[3][i] + "', "
                        + "'" + list[4][i] + "', "
                        + "'" + list[5][i] + "', "
                        + "'" + list[6][i] + "', "
                        + "'" + list[7][i] + "')";

                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();
                }
                //close connection
                this.CloseConnection();
            }
        }
        public void InsertTranscriptCourses(List<string>[] list)
        {
            string query;

            //open connection
            if (this.OpenConnection() == true)
            {
                for (int i = 0; i < list[0].Count(); i++)
                {
                    query = "INSERT INTO TranscriptCourses VALUES("
                        + "'" + list[0][i] + "', "
                        + "'" + list[1][i] + "', "
                        + "'" + list[2][i] + "', "
                        + "'" + list[3][i] + "')";

                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();
                }
                //close connection
                this.CloseConnection();
            }
        }
        public void InsertTransferStudents(List<string>[] list)
        {
            string query;

            //open connection
            if (this.OpenConnection() == true)
            {
                for (int i = 0; i < list[0].Count(); i++)
                {
                    query = "INSERT INTO TransferStudents VALUES('" + list[0][i] + "')";

                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();
                }
                //close connection
                this.CloseConnection();
            }
        }
        public void InsertInternationalStudents(List<string>[] list)
        {
            string query;

            //open connection
            if (this.OpenConnection() == true)
            {
                for (int i = 0; i < list[0].Count(); i++)
                {
                    query = "INSERT INTO InternationalStudents VALUES('" + list[0][i] + "')";

                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();
                }
                //close connection
                this.CloseConnection();
            }
        }
        public void InsertHonorStudents(List<string>[] list)
        {
            string query;

            //open connection
            if (this.OpenConnection() == true)
            {
                for (int i = 0; i < list[0].Count(); i++)
                {
                    query = "INSERT INTO HonorStudents VALUES('" + list[0][i] + "')";

                    //create command and assign the query and connection from the constructor
                    MySqlCommand cmd = new MySqlCommand(query, connection);

                    //Execute command
                    cmd.ExecuteNonQuery();
                }
                //close connection
                this.CloseConnection();
            }
        }

        //Update statement
        public void ExampleUpdate()
        {
            string query = "UPDATE tableinfo SET name='Joe', age='22' WHERE name='John Smith'";

            //Open connection
            if (this.OpenConnection() == true)
            {
                //create mysql command
                MySqlCommand cmd = new MySqlCommand();
                //Assign the query using CommandText
                cmd.CommandText = query;
                //Assign the connection using Connection
                cmd.Connection = connection;

                //Execute query
                cmd.ExecuteNonQuery();

                //close connection
                this.CloseConnection();
            }
        }

        //Delete statement
        public void ExampleDelete()
        {
            string query = "DELETE FROM tableinfo WHERE name='John Smith'";

            if (this.OpenConnection() == true)
            {
                MySqlCommand cmd = new MySqlCommand(query, connection);
                cmd.ExecuteNonQuery();
                this.CloseConnection();
            }
        }

        //Select statement
        public List<string>[] ExampleSelect()
        {
            string query = "SELECT * FROM Student";

            //Create a list to store the result
            List<string>[] list = new List<string>[3];
            list[0] = new List<string>();
            list[1] = new List<string>();
            list[2] = new List<string>();

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["id"] + "");
                    list[1].Add(dataReader["name"] + "");
                    list[2].Add(dataReader["age"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<string>[] SelectHNumbers()
        {
            string query = "SELECT HNumber FROM Student";

            //Create a list to store the result
            List<string>[] list = new List<string>[1];
            list[0] = new List<string>();

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["HNumber"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }
        public List<string>[] SelectCourseIDsForMajor(string ObjectiveName, string CatalogYear)
        {
            string query =

"SELECT CourseID " +
"FROM  " +
	"(SELECT * FROM CourseInHoursReqObjectivePool  " +
	"UNION  " +
	"SELECT * FROM CourseInCourseReqObjPool) AS CoursesPool " +
"WHERE ObjectivePoolID IN " +
	"(SELECT ObjectivePoolID " +
	"FROM ObjectiveRequirement " +
	"WHERE ObjectiveID = " +
		"(SELECT ObjectiveID " +
        "FROM Objective " +
		"WHERE ObjectiveName = '" + ObjectiveName + "' AND CatalogYear = '" + CatalogYear + "'))";

            //Create a list to store the result
            List<string>[] list = new List<string>[1];
            list[0] = new List<string>();

            //Open connection
            if (this.OpenConnection() == true)
            {
                //Create Command
                MySqlCommand cmd = new MySqlCommand(query, connection);
                //Create a data reader and Execute the command
                MySqlDataReader dataReader = cmd.ExecuteReader();

                //Read the data and store them in the list
                while (dataReader.Read())
                {
                    list[0].Add(dataReader["CourseID"] + "");
                }

                //close Data Reader
                dataReader.Close();

                //close Connection
                this.CloseConnection();

                //return list to be displayed
                return list;
            }
            else
            {
                return list;
            }
        }

        //Count statement
        public int ExampleCount()
        {
            string query = "SELECT Count(*) FROM tableinfo";
            int Count = -1;

            //Open Connection
            if (this.OpenConnection() == true)
            {
                //Create Mysql Command
                MySqlCommand cmd = new MySqlCommand(query, connection);

                //ExecuteScalar will return one value
                Count = int.Parse(cmd.ExecuteScalar() + "");

                //close Connection
                this.CloseConnection();

                return Count;
            }
            else
            {
                return Count;
            }
        }
    }
}
